import pandas as pd
from matplotlib import pyplot as plt
#import pylab
import numpy as np
from scipy import stats
from datetime import date, timedelta
import plotly.graph_objects as go
from plotly.subplots import make_subplots
def linear_plot(x,y,col_1,col_2):
"""Plot x,y including a linear regression
Arguments:
x = x-axis variable
y = y-axis variable
col_1: x-axis label
col_2: y-axis label
"""
d=0.1
slope, intercept, r, p, std_err = stats.linregress(x, y)
print('m = {} \ny0 = {} \nr = {}\nstd = {} '.format(slope, intercept, r, p, std_err))
y_prep=list(map(lambda x0:(slope*x0+intercept),x))
fig, ax= plt.subplots()
fig.set_size_inches(8,4)
ax.plot(x,y,color='orange')
ax.plot(x,y_prep,color='green')
ax.set_xlabel(col_1)
ax.set_ylabel(col_2)
ax.set_ylim(y.min()*(1-d),y.max()*(1+d))
plt.show()
ls *xlsx
zsh:1: no matches found: *xlsx
Claims: Providers begin certifying claims on the 1st of the month for the prior data month (i.e. for example, on May 1 providers can begin to certify claims for the April 2022 data month). To be reimbursed within the current month, providers must certify the claims by the 15th of that month.
Enrollment: ACP providers must fully pass through ACP support to eligible households each month in the form of a discount regardless of when they file a claim for reimbursement. Each month, we will update the data to reflect claims certified during the six month window.
Enrollments are already receiving the benefit from providers. We use enrollments to count the total subscribers that will eventually be claimed by providers
infile_c='ACP-Claims-by-County-January-November-2022.xlsx'
df_c=pd.read_excel(infile_c)
df_c=df_c[['data_month','total_claimed_subscribers', 'total_claimed_devices','service_support', 'device_support', 'total_support']]
df_c=df_c.groupby('data_month').sum()
df_c.reset_index(inplace=True)
df_c
| data_month | total_claimed_subscribers | total_claimed_devices | service_support | device_support | total_support | |
|---|---|---|---|---|---|---|
| 0 | 2022-01-01 | 8235728 | 413562 | 3.542841e+08 | 41335415.63 | 3.956195e+08 |
| 1 | 2022-02-01 | 8714543 | 278109 | 3.597353e+08 | 27796265.05 | 3.875316e+08 |
| 2 | 2022-03-01 | 9462909 | 223345 | 2.791492e+08 | 22238593.71 | 3.013877e+08 |
| 3 | 2022-04-01 | 9814091 | 184228 | 2.902180e+08 | 18326126.23 | 3.085441e+08 |
| 4 | 2022-05-01 | 10492334 | 217322 | 3.105463e+08 | 21665562.67 | 3.322118e+08 |
| 5 | 2022-06-01 | 11163104 | 236033 | 3.311573e+08 | 23507522.27 | 3.546648e+08 |
| 6 | 2022-07-01 | 11552438 | 246371 | 3.437182e+08 | 24564871.79 | 3.682830e+08 |
| 7 | 2022-08-01 | 12119733 | 320349 | 3.609515e+08 | 31946118.27 | 3.928976e+08 |
| 8 | 2022-09-01 | 10662298 | 306751 | 3.177272e+08 | 30615462.14 | 3.483427e+08 |
| 9 | 2022-10-01 | 11244496 | 316278 | 3.353980e+08 | 31576549.62 | 3.669746e+08 |
| 10 | 2022-11-01 | 9417839 | 288796 | 2.870551e+08 | 28789289.24 | 3.158444e+08 |
infile_e='ACP-Households-by-County-January-November-2022.xlsx'
df_e=pd.read_excel(infile_e)
df_e=df_e[['Data Month','Net New Enrollments Alternative Verification Process',
'Net New Enrollments Verified by School',
'Net New Enrollments Lifeline',
'Net New Enrollments National Verifier Application',
'Net New Enrollments total', 'Total Alternative Verification Process',
'Total Verified by School', 'Total Lifeline',
'Total National Verifier Application', 'Total Subscribers']]
df_e=df_e.groupby('Data Month').sum().reset_index()
df_e
| Data Month | Net New Enrollments Alternative Verification Process | Net New Enrollments Verified by School | Net New Enrollments Lifeline | Net New Enrollments National Verifier Application | Net New Enrollments total | Total Alternative Verification Process | Total Verified by School | Total Lifeline | Total National Verifier Application | Total Subscribers | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2022-01-01 | 177587.0 | 166.0 | 175850.0 | 297434.0 | 651037.0 | 2162716.0 | 7181.0 | 4592772.0 | 2934588.0 | 9697257.0 |
| 1 | 2022-02-01 | 190787.0 | 86.0 | 208375.0 | 168040.0 | 567288.0 | 2353503.0 | 7267.0 | 4801147.0 | 3102628.0 | 10264545.0 |
| 2 | 2022-03-01 | 197865.0 | -9.0 | 327074.0 | 367012.0 | 891942.0 | 2551368.0 | 7258.0 | 5128221.0 | 3469640.0 | 11156487.0 |
| 3 | 2022-04-01 | 144234.0 | -56.0 | -33729.0 | 309115.0 | 419564.0 | 2695602.0 | 7202.0 | 5094492.0 | 3778755.0 | 11576051.0 |
| 4 | 2022-05-01 | 192531.0 | 64.0 | 57936.0 | 327978.0 | 578509.0 | 2888133.0 | 7266.0 | 5152428.0 | 4106733.0 | 12154560.0 |
| 5 | 2022-06-01 | 200525.0 | -10.0 | -7651.0 | 354273.0 | 547137.0 | 3088659.0 | 7256.0 | 5144776.0 | 4461003.0 | 12701694.0 |
| 6 | 2022-07-01 | 238668.0 | -915.0 | -152898.0 | 318686.0 | 403541.0 | 3327327.0 | 6341.0 | 4991878.0 | 4779689.0 | 13105235.0 |
| 7 | 2022-08-01 | 197208.0 | -32.0 | -146921.0 | 359346.0 | 409601.0 | 3524535.0 | 6309.0 | 4844957.0 | 5139035.0 | 13514836.0 |
| 8 | 2022-09-01 | 193807.0 | -113.0 | 58644.0 | 337160.0 | 589498.0 | 3718342.0 | 6196.0 | 4903601.0 | 5476195.0 | 14104334.0 |
| 9 | 2022-10-01 | 147295.0 | -68.0 | 100204.0 | 341311.0 | 588742.0 | 3865637.0 | 6128.0 | 5003805.0 | 5817506.0 | 14693076.0 |
| 10 | 2022-11-01 | 126847.0 | -67.0 | 18777.0 | 260569.0 | 406126.0 | 3992484.0 | 6061.0 | 5022582.0 | 6078075.0 | 15099202.0 |
Before 2022-08-01 there is linear dependence between enrollments and claims with a gap of $\approx$ $1.74\times 10^6$
#x=np.array(range(1,df_e.shape[0]+1))
x=df_e['Total Subscribers']
y=df_c['total_claimed_subscribers']
col_1='Total Subscribers'
col_2='total_claimed_subscribers'
#x=x[0:8]
#y=y[0:8]
linear_plot(x,y,col_1,col_2)
m = 0.448950370963078 y0 = 4626741.615726177 r = 0.643846319651531 std = 0.032534177161694856
#x=np.array(range(1,df_e.shape[0]+1))
x=df_e['Total Subscribers']
y=df_c['total_claimed_subscribers']
col_1='Total Subscribers'
col_2='total_claimed_subscribers'
x=x[0:8]
y=y[0:8]
linear_plot(x,y,col_1,col_2)
m = 1.0140498434025285 y0 = -1742358.5120452456 r = 0.9961632062320463 std = 1.407974577110122e-07
For claims made after 2022-03-01, there is a flat relationship between total_claimed_subscribers and total_support. We use the mean of the ratio between this two quantities as a proxy for the amount of benefit per suscriber and later per enrollment
\begin{equation} x= \frac{\rm{total\_claimed\_subscribers}}{\rm{total\_claimed\_subscribers}} \end{equation}\begin{equation} \bar{x}\sim \$32 \end{equation}This approximation takes into account the device one time support of $100 for devices
x=np.array(range(1,df_e.shape[0]+1))
y=df_c['total_support']/df_c['total_claimed_subscribers']
col_1='Month'
col_2='total_support/total_claimed_subscribers'
linear_plot(x,y,col_1,col_2)
m = -1.0472420712654509 y0 = 41.04425174339634 r = -0.6021302444574265 std = 0.049970400247901094
#=np.array(range(1,df_e.shape[0]+1))
x=np.array(range(1,df_e.shape[0]+1))
y=df_c['total_support']/df_c['total_claimed_subscribers']
col_1='Month'
col_2='total_support/total_claimed_subscribers'
x=x[2:9]
y=y[2:9]
label='x'
linear_plot(x,y,col_1,col_2)
m = 0.1656618567949997 y0 = 30.9616761830557 r = 0.8249897131023135 std = 0.022342477806664485
monthly_ben=y.mean()
monthly_ben
31.955647323825698
New enrollments/month: there is not "strong" correlation with time
Theh new monthly enrollments is approximated by the mean of pass values: df_e['Net New Enrollments total'].mean() = 550271.36
Note: Time series analysis for weekly enrollments does not show any trends. The Peak of enrollments in March representd in the followin plot can also be seen in the weekly enrollment data.
x=np.array(range(1,df_e.shape[0]+1))
y=df_e['Net New Enrollments total']
col_1='Month'
col_2='Net New Enrollments total'
linear_plot(x,y,col_1,col_2)
m = -20372.409090909096 y0 = 672505.8181818182 r = -0.4660521347444675 std = 0.14850250644644097
monthly_enroll=df_e['Net New Enrollments total'].mean()
monthly_enroll
550271.3636363636
Parameters Total Eligible HH from ESH estimation: 52514583
total_elig=52514583
ebb_enrol=9046220.0
funds=14.2e9
print('Parameters:')
print('Monthly Enrollments= ', monthly_enroll)
print('Monthly Benefit per Enrollment= ',monthly_ben)
print('Total Funds= ',funds)
print('Total Eligible HH = ',total_elig)
Parameters: Monthly Enrollments= 550271.3636363636 Monthly Benefit per Enrollment= 31.955647323825698 Total Funds= 14200000000.0 Total Eligible HH = 52514583
df_p=pd.DataFrame()
df_p['Data Month']=df_e['Data Month']
df_p['Net New Enrollments total']=df_e['Net New Enrollments total']
df_p
| Data Month | Net New Enrollments total | |
|---|---|---|
| 0 | 2022-01-01 | 651037.0 |
| 1 | 2022-02-01 | 567288.0 |
| 2 | 2022-03-01 | 891942.0 |
| 3 | 2022-04-01 | 419564.0 |
| 4 | 2022-05-01 | 578509.0 |
| 5 | 2022-06-01 | 547137.0 |
| 6 | 2022-07-01 | 403541.0 |
| 7 | 2022-08-01 | 409601.0 |
| 8 | 2022-09-01 | 589498.0 |
| 9 | 2022-10-01 | 588742.0 |
| 10 | 2022-11-01 | 406126.0 |
sdate = date(2022,12,1) # start date
edate = date(2026,1,1)
months= pd.date_range(sdate,edate , freq='1M')-pd.offsets.MonthBegin(1)
new_enroll=months.shape[0]*[monthly_enroll]
df_a=pd.DataFrame()
df_a['Data Month']=months
df_a['Net New Enrollments total']=new_enroll
df_p=df_p.append(df_a).reset_index(drop=True)
/var/folders/rh/4xd1337512l1025llqtt0d2h0000gn/T/ipykernel_36622/1256441189.py:1: FutureWarning: The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.
df_p.head(14)
| Data Month | Net New Enrollments total | |
|---|---|---|
| 0 | 2022-01-01 | 651037.000000 |
| 1 | 2022-02-01 | 567288.000000 |
| 2 | 2022-03-01 | 891942.000000 |
| 3 | 2022-04-01 | 419564.000000 |
| 4 | 2022-05-01 | 578509.000000 |
| 5 | 2022-06-01 | 547137.000000 |
| 6 | 2022-07-01 | 403541.000000 |
| 7 | 2022-08-01 | 409601.000000 |
| 8 | 2022-09-01 | 589498.000000 |
| 9 | 2022-10-01 | 588742.000000 |
| 10 | 2022-11-01 | 406126.000000 |
| 11 | 2022-12-01 | 550271.363636 |
| 12 | 2023-01-01 | 550271.363636 |
| 13 | 2023-02-01 | 550271.363636 |
df_p['Total Subscribers']=df_p['Net New Enrollments total'].cumsum()+ebb_enrol
df_p['Totat Support']=df_p['Total Subscribers']*monthly_ben
df_p['Totat Support cum']=df_p['Totat Support'].cumsum()
df_p['Total Support cum norm']=df_p['Totat Support cum']/funds*100
df_p['Enrollment Pct']=df_p['Total Subscribers']/total_elig*100
df_p
| Data Month | Net New Enrollments total | Total Subscribers | Totat Support | Totat Support cum | Total Support cum norm | Enrollment Pct | |
|---|---|---|---|---|---|---|---|
| 0 | 2022-01-01 | 651037.000000 | 9.697257e+06 | 3.098821e+08 | 3.098821e+08 | 2.182268 | 18.465836 |
| 1 | 2022-02-01 | 567288.000000 | 1.026454e+07 | 3.280102e+08 | 6.378923e+08 | 4.492199 | 19.546085 |
| 2 | 2022-03-01 | 891942.000000 | 1.115649e+07 | 3.565128e+08 | 9.944051e+08 | 7.002853 | 21.244550 |
| 3 | 2022-04-01 | 419564.000000 | 1.157605e+07 | 3.699202e+08 | 1.364325e+09 | 9.607924 | 22.043498 |
| 4 | 2022-05-01 | 578509.000000 | 1.215456e+07 | 3.884068e+08 | 1.752732e+09 | 12.343184 | 23.145114 |
| 5 | 2022-06-01 | 547137.000000 | 1.270170e+07 | 4.058909e+08 | 2.158623e+09 | 15.201571 | 24.186990 |
| 6 | 2022-07-01 | 403541.000000 | 1.310524e+07 | 4.187864e+08 | 2.577409e+09 | 18.150771 | 24.955426 |
| 7 | 2022-08-01 | 409601.000000 | 1.351484e+07 | 4.318754e+08 | 3.009285e+09 | 21.192147 | 25.735402 |
| 8 | 2022-09-01 | 589498.000000 | 1.410434e+07 | 4.507132e+08 | 3.459998e+09 | 24.366184 | 26.857943 |
| 9 | 2022-10-01 | 588742.000000 | 1.469308e+07 | 4.695269e+08 | 3.929525e+09 | 27.672711 | 27.979045 |
| 10 | 2022-11-01 | 406126.000000 | 1.509920e+07 | 4.825049e+08 | 4.412030e+09 | 31.070632 | 28.752404 |
| 11 | 2022-12-01 | 550271.363636 | 1.564948e+07 | 5.000891e+08 | 4.912119e+09 | 34.592387 | 29.800249 |
| 12 | 2023-01-01 | 550271.363636 | 1.619975e+07 | 5.176734e+08 | 5.429792e+09 | 38.237974 | 30.848094 |
| 13 | 2023-02-01 | 550271.363636 | 1.675002e+07 | 5.352577e+08 | 5.965050e+09 | 42.007395 | 31.895938 |
| 14 | 2023-03-01 | 550271.363636 | 1.730029e+07 | 5.528420e+08 | 6.517892e+09 | 45.900648 | 32.943783 |
| 15 | 2023-04-01 | 550271.363636 | 1.785056e+07 | 5.704263e+08 | 7.088318e+09 | 49.917735 | 33.991628 |
| 16 | 2023-05-01 | 550271.363636 | 1.840083e+07 | 5.880105e+08 | 7.676329e+09 | 54.058654 | 35.039473 |
| 17 | 2023-06-01 | 550271.363636 | 1.895110e+07 | 6.055948e+08 | 8.281924e+09 | 58.323406 | 36.087318 |
| 18 | 2023-07-01 | 550271.363636 | 1.950138e+07 | 6.231791e+08 | 8.905103e+09 | 62.711991 | 37.135163 |
| 19 | 2023-08-01 | 550271.363636 | 2.005165e+07 | 6.407634e+08 | 9.545866e+09 | 67.224409 | 38.183008 |
| 20 | 2023-09-01 | 550271.363636 | 2.060192e+07 | 6.583476e+08 | 1.020421e+10 | 71.860660 | 39.230853 |
| 21 | 2023-10-01 | 550271.363636 | 2.115219e+07 | 6.759319e+08 | 1.088015e+10 | 76.620744 | 40.278697 |
| 22 | 2023-11-01 | 550271.363636 | 2.170246e+07 | 6.935162e+08 | 1.157366e+10 | 81.504661 | 41.326542 |
| 23 | 2023-12-01 | 550271.363636 | 2.225273e+07 | 7.111005e+08 | 1.228476e+10 | 86.512411 | 42.374387 |
| 24 | 2024-01-01 | 550271.363636 | 2.280300e+07 | 7.286848e+08 | 1.301345e+10 | 91.643994 | 43.422232 |
| 25 | 2024-02-01 | 550271.363636 | 2.335328e+07 | 7.462690e+08 | 1.375972e+10 | 96.899410 | 44.470077 |
| 26 | 2024-03-01 | 550271.363636 | 2.390355e+07 | 7.638533e+08 | 1.452357e+10 | 102.278658 | 45.517922 |
| 27 | 2024-04-01 | 550271.363636 | 2.445382e+07 | 7.814376e+08 | 1.530501e+10 | 107.781740 | 46.565767 |
| 28 | 2024-05-01 | 550271.363636 | 2.500409e+07 | 7.990219e+08 | 1.610403e+10 | 113.408654 | 47.613612 |
| 29 | 2024-06-01 | 550271.363636 | 2.555436e+07 | 8.166061e+08 | 1.692064e+10 | 119.159402 | 48.661456 |
| 30 | 2024-07-01 | 550271.363636 | 2.610463e+07 | 8.341904e+08 | 1.775483e+10 | 125.033982 | 49.709301 |
| 31 | 2024-08-01 | 550271.363636 | 2.665490e+07 | 8.517747e+08 | 1.860660e+10 | 131.032396 | 50.757146 |
| 32 | 2024-09-01 | 550271.363636 | 2.720517e+07 | 8.693590e+08 | 1.947596e+10 | 137.154642 | 51.804991 |
| 33 | 2024-10-01 | 550271.363636 | 2.775545e+07 | 8.869433e+08 | 2.036290e+10 | 143.400721 | 52.852836 |
| 34 | 2024-11-01 | 550271.363636 | 2.830572e+07 | 9.045275e+08 | 2.126743e+10 | 149.770633 | 53.900681 |
| 35 | 2024-12-01 | 550271.363636 | 2.885599e+07 | 9.221118e+08 | 2.218954e+10 | 156.264379 | 54.948526 |
| 36 | 2025-01-01 | 550271.363636 | 2.940626e+07 | 9.396961e+08 | 2.312924e+10 | 162.881957 | 55.996370 |
| 37 | 2025-02-01 | 550271.363636 | 2.995653e+07 | 9.572804e+08 | 2.408652e+10 | 169.623368 | 57.044215 |
| 38 | 2025-03-01 | 550271.363636 | 3.050680e+07 | 9.748646e+08 | 2.506138e+10 | 176.488612 | 58.092060 |
| 39 | 2025-04-01 | 550271.363636 | 3.105707e+07 | 9.924489e+08 | 2.605383e+10 | 183.477689 | 59.139905 |
| 40 | 2025-05-01 | 550271.363636 | 3.160735e+07 | 1.010033e+09 | 2.706386e+10 | 190.590598 | 60.187750 |
| 41 | 2025-06-01 | 550271.363636 | 3.215762e+07 | 1.027617e+09 | 2.809148e+10 | 197.827341 | 61.235595 |
| 42 | 2025-07-01 | 550271.363636 | 3.270789e+07 | 1.045202e+09 | 2.913668e+10 | 205.187917 | 62.283440 |
| 43 | 2025-08-01 | 550271.363636 | 3.325816e+07 | 1.062786e+09 | 3.019947e+10 | 212.672326 | 63.331285 |
| 44 | 2025-09-01 | 550271.363636 | 3.380843e+07 | 1.080370e+09 | 3.127984e+10 | 220.280567 | 64.379129 |
| 45 | 2025-10-01 | 550271.363636 | 3.435870e+07 | 1.097955e+09 | 3.237780e+10 | 228.012642 | 65.426974 |
| 46 | 2025-11-01 | 550271.363636 | 3.490897e+07 | 1.115539e+09 | 3.349333e+10 | 235.868549 | 66.474819 |
| 47 | 2025-12-01 | 550271.363636 | 3.545925e+07 | 1.133123e+09 | 3.462646e+10 | 243.848290 | 67.522664 |
ro=df_p[df_p['Total Support cum norm']<=100]
p=df_p.iloc[ro.shape[0]-1]
p
Data Month 2024-02-01 00:00:00 Net New Enrollments total 550271.363636 Total Subscribers 23353275.454545 Totat Support 746269034.28161 Totat Support cum 13759716149.178501 Total Support cum norm 96.89941 Enrollment Pct 44.470077 Name: 25, dtype: object
primary="% Total Funds"
secondary="% Enrollment"
fig = make_subplots(specs=[[{"secondary_y": True}]])
col_1='Total Support cum norm'
fig.add_trace(
go.Scatter(x=df_p['Data Month'],y=df_p[col_1],name=primary),
secondary_y=False,
)
fig.add_trace(
go.Scatter(x=[p[0],p[0]],y=[0,200],name="Date for Funds Running Out"),
secondary_y=False,
)
col_2='Enrollment Pct'
fig.add_trace(
go.Scatter(x=df_p['Data Month'],y=df_p[col_2], name=secondary),
secondary_y=True,
)
fig.update_layout(xaxis_range=['2022-01-01','2025-01-01'],yaxis_range=[0,120],title_text="ACP Digital Cliff")
fig.update_yaxes(
title_text=primary,
secondary_y=False)
fig.update_yaxes(
title_text=secondary,
secondary_y=True)
Update with the current total support